package com.navi.loaddata.loadService.impl.DefectData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.MinMaxEvttimeStamp;
import com.navi.loaddata.core.gpEntity.YearMonth;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.navi.loaddata.core.util.LoggerUtil;
import org.hibernate.exception.GenericJDBCException;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


/**
 * @author fuchenyang
 * @date 2018/4/19
 */
@Service
public class LoadDefectData_AService {
    private static final Logger logger = LoggerFactory.getLogger(LoadDefectData_AService.class);

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private LoadDefectDataBean loadDefectDataBean;


    public String[] loadDefectDataMain(EtlConfDModel etlConfDModel, EtlConfD etlConfD) throws Exception {
        String[] resMsg = loadDefectData(etlConfDModel, etlConfD);
        String msg = etlConfDModel.getOpe();
        if (org.apache.commons.lang.StringUtils.isEmpty(etlConfDModel.getOpe())) msg = "ALL";
        logger.info("loadDefectData_A "+  "wpp_"+ etlConfDModel.getOpe() +"_adefect_sum" +" finish!", msg);
        return resMsg;
    }

    private String[] loadDefectData(EtlConfDModel etlConfDModel, EtlConfD etlConfD) throws Exception{
        String[] resMsg = new String[3];
        int okCnt = 0;
        int errCnt = 0;
        String errMsg = null;

        //找到需要插入数据的表
        String tmpTab = "wpp_"+ etlConfDModel.getOpe() +"_adefect_sum";

        //判断表是否已存在，若不存在，则将数据插入到wpp_default_adefect_sum表中
        List<String> tabList = getTabList(etlConfDModel);

        if(tabList.size() == 0 || tabList == null) {
            tmpTab = "wpp_default_adefect_sum";
        }

        String defectShop = "LoadDefectData_A";

        List<YearMonth> yearMonthList = loadDefectDataBean.getYearMonth(etlConfDModel, tmpTab);
        List<YearMonth> yearMonthNewList = new ArrayList<>();

        //panel表中查不到对应的日期，则说明没有对应数据，返回为空
//        if (yearMonthList.get(0).getMonth() == null) {
//            resMsg[0] = "0";
//            resMsg[1] = "0";
//            resMsg[2] = null;
//
//            return resMsg;
//        }
        if (yearMonthList.size() == 0) {
            resMsg[0] = "0";
            resMsg[1] = "0";
            resMsg[2] = "查询时间" + etlConfDModel.getRunStartTimestamp() + "到" + etlConfDModel.getRunEndTimestamp() + "内，无符合Glass命名规则的glass_id";

            return resMsg;
        } else {
            //去除不符合规则的数据，若yearMonthList还有数据则继续后续逻辑，若yearMonthList无数据，则返回
            for (YearMonth yearMonth : yearMonthList) {
                if (yearMonth.getMonth() != null && yearMonth.getYear() != null) {
                    if (yearMonth.getYear().matches("[0-9]{2,}") && (yearMonth.getMonth().matches("[1-9]") || yearMonth.getMonth().matches("[A-C]"))) {
                        yearMonthNewList.add(yearMonth);
                    }
                }
            }

            if (yearMonthNewList.size() == 0) {
                resMsg[0] = "0";
                resMsg[1] = "0";
                resMsg[2] = "查询时间" + etlConfDModel.getRunStartTimestamp() + "到" + etlConfDModel.getRunEndTimestamp() + "内，无符合Glass命名规则的glass_id";

                return resMsg;
            }
        }

        //获取by db_timestamp时间段内的最小和最大的evt_timestamp，作为panel查询时间段
        List<MinMaxEvttimeStamp> minMaxEvttimeStamp = loadDefectDataBean.getMinMaxEvtTime(etlConfDModel, tmpTab);

        String EvtStartTime = "";
        String EvtEndTime = "";
        String MaxEndTime = "";
        Boolean loopFlag = false;

        if (minMaxEvttimeStamp.get(0).getMinTime() == null || minMaxEvttimeStamp.get(0).getMaxTime() == null) {
            resMsg[0] = "0";
            resMsg[1] = "0";
            resMsg[2] = "查询时间" + etlConfDModel.getRunStartTimestamp() + "到" + etlConfDModel.getRunEndTimestamp() + "内，evt_timestamp为空";

            return resMsg;
        } else {
            SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date minTime = new Date();
            Date maxTime = new Date();
            Date limitMinTime = new Date();

            try {
                minTime = sqldate.parse(minMaxEvttimeStamp.get(0).getMinTime());
                maxTime = sqldate.parse(minMaxEvttimeStamp.get(0).getMaxTime());
                limitMinTime = sqldate.parse("2018-03-01 00:00:00");
            } catch (ParseException e) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
                e.printStackTrace();
            }

            //若查询时间段最小evt_timestamp小于2018-03-01 00:00:00，则最小时间用2018-03-01 00:00:00，否则就用查询获取的最小时间
            if (limitMinTime.before(minTime)) {
                EvtStartTime = minMaxEvttimeStamp.get(0).getMinTime();
            } else {
                EvtStartTime = "2018-03-01 00:00:00";
            }

            try {
                minTime = sqldate.parse(EvtStartTime);
            } catch (ParseException e) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
                e.printStackTrace();
            }

            //若起始时间距离结束时间大于一个月，则将时间按月分割进行查询及数据删除插入操作
            while ((maxTime.getTime() - minTime.getTime())/(24*60*60*1000) > 30) {
                loopFlag = true;

                Calendar calStart = Calendar.getInstance();
                calStart.setTime(minTime);
                calStart.add(Calendar.MONTH, 1);
                calStart.set(Calendar.DAY_OF_MONTH, 1);
                EvtStartTime = sqldate.format(minTime);
                EvtEndTime = sqldate.format(calStart.getTime());
                MaxEndTime = sqldate.format(maxTime);

//                //删除要跑时间段内的数据
//                loadDefectDataBean.deletePeriodData(etlConfDModel, tmpTab, defectShop， EvtStartTime, EvtEndTime);

                try {
                    //删除DefectSummary表中存在的重复数据（By OpeId和PanelId）
                    if (yearMonthNewList.size() > 0) {
                        loadDefectDataBean.deleteDuplicateData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthNewList);
                    } else {
                        loadDefectDataBean.deleteDuplicateData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthList);
                    }
                    //向DefectSummary表中插入数据
                    loadDefectDataBean.insertData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, okCnt, loopFlag);
                } catch (Exception e) {
                    String errorMsg = e.toString();
                    if (errorMsg.indexOf("GenericJDBCException") > -1) {
                        if (e.getCause().toString().indexOf("deadlock") > -1) {
                            etlConfD.setRunFlg("N");
                            etlConfD.setExt2("N");
                            etlConfD.setRunEndTimestamp(null);
                            loadDefectDataBean.updateEtlConfD(etlConfD);

                            resMsg[0] = "0";
                            resMsg[1] = "0";
                            resMsg[2] = e.getCause().toString();

                            return resMsg;
                        } else {
                            logger.error("数据库insert/delete出现GenericJDBCException报错", e);
                            throw e;
                        }
                    } else {
                        logger.error("数据库insert/delete出现报错", e);
                        throw e;
                    }
                }

                try {
                    minTime = sqldate.parse(EvtEndTime);
                } catch (ParseException e) {
                    logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
                    e.printStackTrace();
                }
            }

            //若已经进入by month循环当中，则进行最后一次数据删除插入操作，若没有，则直接用最小时间作为起始时间进行数据删除插入操作
            if (loopFlag) {
                EvtStartTime = sqldate.format(minTime);
                EvtEndTime = minMaxEvttimeStamp.get(0).getMaxTime();
                loopFlag = false;

                try {
                    //删除DefectSummary表中存在的重复数据（By OpeId和PanelId）
                    if (yearMonthNewList.size() > 0) {
                        loadDefectDataBean.deleteDuplicateData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthNewList);
                    } else {
                        loadDefectDataBean.deleteDuplicateData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthList);
                    }

                    //向DefectSummary表中插入数据
                    loadDefectDataBean.insertData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, okCnt, loopFlag);
                } catch (Exception e) {
                    String errorMsg = e.toString();
                    if (errorMsg.indexOf("GenericJDBCException") > -1) {
                        if (e.getCause().toString().indexOf("deadlock") > -1) {
                            etlConfD.setRunFlg("N");
                            etlConfD.setExt2("N");
                            etlConfD.setRunEndTimestamp(null);
                            loadDefectDataBean.updateEtlConfD(etlConfD);

                            resMsg[0] = "0";
                            resMsg[1] = "0";
                            resMsg[2] = e.getCause().toString();

                            return resMsg;
                        } else {
                            logger.error("数据库insert/delete出现GenericJDBCException报错", e);
                            throw e;
                        }
                    } else {
                        logger.error("数据库insert/delete出现报错", e);
                        throw e;
                    }
                }
            } else {
                EvtEndTime = minMaxEvttimeStamp.get(0).getMaxTime();

                try {
//                    PSQLState state = PSQLState.CANNOT_COERCE;
//                    SQLException sqlException = new PSQLException("deadlock detected", state);
//                    throw new GenericJDBCException("测试抛GenericJDBCException的catch逻辑", sqlException);
                    //删除DefectSummary表中存在的重复数据（By OpeId和PanelId）
                    if (yearMonthNewList.size() > 0) {
                        loadDefectDataBean.deleteDuplicateData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthNewList);
                    } else {
                        loadDefectDataBean.deleteDuplicateData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthList);
                    }

                    //向DefectSummary表中插入数据
                    loadDefectDataBean.insertData(etlConfDModel, tmpTab, defectShop, EvtStartTime, EvtEndTime, errMsg, okCnt, loopFlag);
                } catch (Exception e) {
                    String errorMsg = e.toString();
                    if (errorMsg.indexOf("GenericJDBCException") > -1) {
                        if (e.getCause().toString().indexOf("deadlock") > -1) {
                            etlConfD.setRunFlg("N");
                            etlConfD.setExt2("N");
                            etlConfD.setRunEndTimestamp(null);
                            loadDefectDataBean.updateEtlConfD(etlConfD);

                            resMsg[0] = "0";
                            resMsg[1] = "0";
                            resMsg[2] = e.getCause().toString();

                            return resMsg;
                        } else {
                            logger.error("数据库insert/delete出现GenericJDBCException报错", e);
                            throw e;
                        }
                    } else {
                        logger.error("数据库insert/delete出现报错", e);
                        throw e;
                    }
                }
            }
        }

        resMsg[0] = String.valueOf(okCnt);
        resMsg[1] = String.valueOf(errCnt);
        resMsg[2] = errMsg;

        return resMsg;
    }

//    public List<String> getOpeList(EtlConfDModel etlConfDModel) throws ETLException2 {
//        List<String> opeList = new ArrayList<>();
//
//        //查询数据当中所有的站点，后续作为插入数据到各站点的Defect Summary表的参考
//        StringBuffer sqlbuffSum = new StringBuffer();
//        sqlbuffSum.append("select distinct ope_id");
//        sqlbuffSum.append(" from wpp_adefect_f");
//
//        //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
//        if(etlConfDModel.getExt1().toString().equals("Y")) {
//            sqlbuffSum.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
//            sqlbuffSum.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
//        }
//        else {
//            SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
//            Date startDate = new Date();
//            Date endDate = new Date();
//            try{
//                startDate = sqldate.parse(etlConfDModel.getRunStartTimestamp().toString());
//                endDate = sqldate.parse(etlConfDModel.getRunEndTimestamp().toString());
//            }
//            catch (ParseException e) {
//                e.printStackTrace();
//            }
//            Calendar calStart = Calendar.getInstance();
//            calStart.setTime(startDate);
//            calStart.add(Calendar.MONTH, -1);
//
//            Calendar calEnd = Calendar.getInstance();
//            calEnd.setTime(endDate);
//            calEnd.add(Calendar.DATE, 10);
//
//            sqlbuffSum.append(" where evt_timestamp >= '" + sqldate.format(calStart.getTime()) + "'");
//            sqlbuffSum.append(" and evt_timestamp < '" + sqldate.format(calEnd.getTime()) + "'");
//            sqlbuffSum.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
//            sqlbuffSum.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
//        }
//
//        sqlbuffSum.append(" and real_flg = 'Y'");
////        sqlbuffSum.append(" and defect_code not like '*%'");
//
//        try {
//            logger.info("opeList SQL:" + sqlbuffSum.toString());
//
//            Date startTime = new Date();
//
//            opeList = gpDao.findBySQL(sqlbuffSum.toString());  //获取表中所有列名
//
//            Date endTime = new Date();
//            long durableTime = (endTime.getTime() - startTime.getTime())/1000;
//            logger.info("查opeList耗时:" + durableTime +"s");
//
//            if(opeList.isEmpty()) opeList.add(null);
//        } catch (Exception e) {
//            logger.error(LoggerUtil.stackTraceToString(e));
//            throw new ETLException2(ErrorCode.ERROR_GET_GP_DEFECT_OPE,e);
//        }
//
//        return opeList;
//    }

    public List<String> getTabList(EtlConfDModel etlConfDModel) throws ETLException2 {
        List<String> tabList = new ArrayList<>();

        StringBuffer sqlTabDef = new StringBuffer();

//        sqlTabDef.append("select table_name from information_schema.columns ");
//        sqlTabDef.append(" where table_schema='sor'");
//        sqlTabDef.append(" and table_name = 'wpp_"+ etlConfDModel.getOpe().toLowerCase() +"_adefect_sum'");
        sqlTabDef.append("select cast(tablename as varchar) from pg_catalog.pg_tables ");
        sqlTabDef.append(" where tablename = 'wpp_"+ etlConfDModel.getOpe().toLowerCase() +"_adefect_sum'");

        try {
            logger.info("tabList SQL:" + sqlTabDef.toString());

            Date startTime = new Date();

            tabList = gpDao.findBySQL(sqlTabDef.toString()); //查看是否有对应的表

            Date endTime = new Date();
            long durableTime = (endTime.getTime() - startTime.getTime())/1000;
            logger.info("查tabList耗时:" + durableTime +"s");
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException2(ErrorCode.ERROR_GET_GP_DEFECT_TAB, e);
        }

        return tabList;
    }
}
